package com.bdy.lm.browser.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.bdy.lm.browser.domain.OperationMsg;

public class OperationQueryDao {
	
	private Connection con = null;
	private ResultSet rs = null;
	private PreparedStatement ps = null;
	private QueryRunner qr = new TxQueryRunner();

	public List<OperationMsg> queryOperation(String queryMode, String queryContent) {
		
		String sql = "SELECT car_tb.license,company_tb.companyName,driver_tb.driverName,transaction_tb.* FROM car_tb,company_tb," +
				"driver_tb,transaction_tb WHERE car_tb.ISUFlag=transaction_tb.ISUFlag AND car_tb.companyId=company_tb.companyId AND " +
				"driver_tb.ICID=transaction_tb.ICID";
		Object[] param = { queryContent };
		try {
			if (queryMode.equals("all")) {
				return qr.query(sql, new BeanListHandler<OperationMsg>(OperationMsg.class));			
			} else if (queryMode.equals("companyName")) {
				String cn = sql + "AND companyName=?";
				return qr.query(cn, new BeanListHandler<OperationMsg>(OperationMsg.class), param);
			} else if (queryMode.equals("license")) {
				String l = sql + "AND license=?";
				return qr.query(l, new BeanListHandler<OperationMsg>(OperationMsg.class), param);
			} else {
				String d = sql + "AND driverName=?";
				return qr.query(d, new BeanListHandler<OperationMsg>(OperationMsg.class), param);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public byte[] queryCheatMonitor(String license, String getOnTime) throws SQLException{
		String sql = "SELECT transaction_tb.locationTracks FROM car_tb,transaction_tb " +
					 "WHERE car_tb.ISUFlag=transaction_tb.ISUFlag AND car_tb.license='" +
					 license + "' AND transaction_tb.getOnTime='" + getOnTime + "'";
		byte[] locationTracks = null;
        try {
            con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
            	locationTracks = new byte[rs.getBinaryStream("locationTracks").available()];
                rs.getBinaryStream("locationTracks").read(locationTracks);
            }
            return locationTracks;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
			 if (rs != null)
			 rs.close();
			 if (con != null)
			 con.close();
			 if (ps != null)
			 con.close();
        }
	}
}
